package com.zhuanzhuan.dao.impl;
import com.zhuanzhuan.dao.BaseDao;
import com.zhuanzhuan.dao.GoodsDao;
import com.zhuanzhuan.entity.Goods;
import com.zhuanzhuan.vo.GoodsDetail;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class GoodsDaoMySQLImpl extends BaseDao implements GoodsDao {

    //获取在售的好物信息
    @Override
    public List<GoodsDetail> listOnSellGoods(int page, int type) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<GoodsDetail> listGoods = new ArrayList<GoodsDetail>();

        conn = getConnection();
        try {
            //构造PreparedStatement对象
            String filterType = "";
            //判断是否需要按好物类型查询
            if(type != -1){
                filterType="AND typeID = ?";
            }
            String sql = "SELECT goods.id,goods.name,price,description,addTime,type.name as typeName,user.name as ownerName,ownerID FROM goods JOIN user ON goods.ownerID = user.id JOIN type ON goods.typeID = type.id  WHERE isOnSell = true"+ filterType +" limit ?,5; ";
            pstmt = conn.prepareStatement(sql);
            //查询条件不同，参数个数不同
            if(type != -1){
                pstmt.setInt(1, type);
                pstmt.setInt(2, 5 * page);
            }else {
                pstmt.setInt(1, 5 * page);
            }
            rs = pstmt.executeQuery();
            //读取好物信息
            while(rs.next()){
                //从MySQL读取好物信息，并加载到goods对象中
                GoodsDetail goods= new GoodsDetail();
                goods.setName(rs.getString("name"));
                goods.setPrice(rs.getFloat("price"));
                goods.setDescription(rs.getString("description"));
                goods.setAddTime(rs.getDate("addTime"));
                goods.setType(rs.getString("typeName"));
                goods.setOwner(rs.getString("ownerName"));
                goods.setOwnerID(rs.getInt("ownerID"));
                goods.setId(rs.getInt("id"));
                listGoods.add(goods);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭数据库连接
            closeAll(conn, pstmt, rs);
        }
        return listGoods;
    }

    //按照所有者ID获取好物列表
    @Override
    public List<GoodsDetail> listOnSellGoodsByUserID(int userId, int page) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<GoodsDetail> listGoods = new ArrayList<GoodsDetail>();

        conn = getConnection();
        try {
            //构造PreparedStatement对象
            String sql = "SELECT goods.id,goods.name,price,description,addTime,type.name as typeName,user.name as ownerName,ownerID FROM goods JOIN user ON goods.ownerID = user.id JOIN type ON goods.typeID = type.id  WHERE isOnSell = true AND ownerID=? limit ?,5; ";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            pstmt.setInt(2, 5 * page);
            rs = pstmt.executeQuery();
            while(rs.next()){
                GoodsDetail goods= new GoodsDetail();
                goods.setName(rs.getString("name"));
                goods.setPrice(rs.getFloat("price"));
                goods.setDescription(rs.getString("description"));
                goods.setAddTime(rs.getDate("addTime"));
                goods.setType(rs.getString("typeName"));
                goods.setOwner(rs.getString("ownerName"));
                goods.setOwnerID(rs.getInt("ownerID"));
                goods.setId(rs.getInt("id"));
                listGoods.add(goods);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭数据库连接
            closeAll(conn, pstmt, rs);
        }
        return listGoods;
    }

    //上架新的好物
    @Override
    public int addNewGoods(Goods goods) {
        //构造SQL语句
        String preparedSQL = "INSERT goods (name,price,description,addTime,isOnSell,typeID,ownerID) VALUES (?,?,?,?,?,?,?)";
        //构造SQL执行参数数组
        List<Object> params = new ArrayList<Object>();
        params.add(goods.getName());
        params.add(goods.getPrice());
        params.add(goods.getDescription());
        params.add(goods.getAddTime());
        params.add(goods.isOnSell());
        params.add(goods.getTypeID());
        params.add(goods.getOwnerID());
        //调用BaseDao中的更新
        return exceuteUpdate(preparedSQL, params.toArray());
    }

    //获取在售好物的个数
    @Override
    public int getOnSellGoodsCountByType(int type){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int goodsCount = 0;

        conn = getConnection();
        try {
            //构造PreparedStatement对象
            String filterType = "";
            if(type != -1){
                filterType="AND typeID=?";
            }
            String sql = "SELECT count(id) FROM goods WHERE isOnSell = true"+ filterType;
            pstmt = conn.prepareStatement(sql);
            if(type != -1){
                pstmt.setInt(1, type);
            }
            rs = pstmt.executeQuery();
            if(rs.next()){
                goodsCount = rs.getInt(1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭数据库连接
            closeAll(conn, pstmt, rs);
        }
        return goodsCount;
    }

    //获取指定用户在售好物个数
    @Override
    public int getOnSellGoodsCountByUserID(int userID){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int goodsCount = 0;

        conn = getConnection();
        try {
            //构造PreparedStatement对象
            String sql = "SELECT count(id) FROM goods WHERE isOnSell = true AND ownerID=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userID);
            rs = pstmt.executeQuery();
            if(rs.next()){
                goodsCount = rs.getInt(1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭数据库连接
            closeAll(conn, pstmt, rs);
        }
        return goodsCount;
    }

    //更新好物信息
    @Override
    public int updateGoodsOnSellState(int GoodsID, boolean isOnSell){
        //构造SQL语句
        String preparedSQL = "UPDATE goods SET isOnSell = ? WHERE id = ?";
        //构造SQL执行参数数组
        List<Object> params = new ArrayList<Object>();
        params.add(isOnSell);
        params.add(GoodsID);
        //调用BaseDao中的更新
        return exceuteUpdate(preparedSQL, params.toArray());
    }
}
